﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IBLL;
using MisDLL;
using System.Data;

namespace MisBLL
{
    public class CustomerDAO : BaseDAO, ICustomer
    {

        //得到最后最大的ID
        public int FindLastId()
        {
            var cusMax = (from c in dbContext.Customer
                          where !(new string[] { "5666", "6666", "9999" }).Contains(c.CUS_ID)
                          select c.CUS_ID)
                   .Max();
            return Int32.Parse(cusMax)+1;
        }

        public MisEntity.Customer Load(string Id)
        {
            var query = from item in dbContext.Customer
                        where item.CUS_ID == Id
                        select item;
            if (query.Count<MisEntity.Customer>() == 0)
                return null;
            return query.First<MisEntity.Customer>();
        }


        public IList<MisEntity.Customer> FindBusinessMan()
        {
            var query = from item in dbContext.Customer
                        where item.LINKER != ""
                        select item;
            if (query.Count<MisEntity.Customer>() == 0)
                return null;
            return query.ToList<MisEntity.Customer>();
        }

        public IList<MisEntity.Customer> FindBusinessMan1()
        {
            var query = from item in dbContext.Customer
                        where item.F2 != ""
                        select item;
            if (query.Count<MisEntity.Customer>() == 0)
                return null;
            return query.ToList<MisEntity.Customer>();
        }

        //根据编码查询客户档案信息
        public MisModel.Customer GetById(string id)
        {
            if (string.IsNullOrEmpty(id))
                return null;
            string sql = string.Format("select * from CUSTOMER_TAB where CUS_ID='{0}'", id);
            DataTable dt=  dbHelper.ExecuteDataTable(sql);
            MisModel.Customer entity = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                entity = new MisModel.Customer();
                entity.CusID = dt.Rows[0]["CUS_ID"].ToString();
                entity.Customer_ = dt.Rows[0]["CUSTOMER"].ToString();
                entity.AllName = dt.Rows[0]["ALLNAME"].ToString();
                entity.Addr = dt.Rows[0]["ADDR"].ToString();
                entity.Post = dt.Rows[0]["POST"].ToString();
                entity.Tele = dt.Rows[0]["TELE"].ToString();
                entity.Bei = dt.Rows[0]["BEI"].ToString();
                entity.Liker = dt.Rows[0]["LINKER"].ToString();
                entity.Bank = dt.Rows[0]["BANK"].ToString();
                entity.BankNo = dt.Rows[0]["BANKNO"].ToString();
                entity.TaxNo = dt.Rows[0]["TAXNO"].ToString();
                entity.F1 = dt.Rows[0]["F1"].ToString();
                entity.F2 = dt.Rows[0]["F2"].ToString();
                entity.F3 = dt.Rows[0]["F3"].ToString();
                entity.F4 = dt.Rows[0]["F4"].ToString();
                entity.F5 = dt.Rows[0]["F5"].ToString();
                entity.F6 = dt.Rows[0]["F6"].ToString();
                entity.F7 = dt.Rows[0]["F7"].ToString();
                entity.F8 = dt.Rows[0]["F8"].ToString();
                entity.FaiThJe = decimal.Parse(dt.Rows[0]["FAITHJE"].ToString());
                entity.DateS = DateTime.Parse(dt.Rows[0]["DATE_s"].ToString());
                entity.DateE = DateTime.Parse(dt.Rows[0]["DATE_e"].ToString());
                entity.FaiTh = dt.Rows[0]["FAITH"].ToString();
                entity.Debt = dt.Rows[0]["DEBT"].ToString();
                entity.WayJS = dt.Rows[0]["WAY_JS"].ToString();
                entity.Sex = dt.Rows[0]["SEX"].ToString();
                entity.Age = decimal.Parse(dt.Rows[0]["AGE"].ToString());
                entity.Prof = dt.Rows[0]["PROF"].ToString();
                entity.DateL = DateTime.Parse(dt.Rows[0]["DATE_L"].ToString());
                entity.Birthday = DateTime.Parse(dt.Rows[0]["BIRTHDAY"].ToString());
                entity.MenID = dt.Rows[0]["MENID"].ToString();
                entity.Oper = dt.Rows[0]["OPER"].ToString();
                entity.SumJeBuy = decimal.Parse(dt.Rows[0]["SUMJEBUY"].ToString());
                entity.SumJeDed = decimal.Parse(dt.Rows[0]["SUMJEDED"].ToString());
                entity.CType = dt.Rows[0]["CTYPE"].ToString();
                entity.MM = dt.Rows[0]["MM"].ToString();
                entity.YcDebt = decimal.Parse(dt.Rows[0]["YCDEBT"].ToString());
                entity.JeBz1 = decimal.Parse(dt.Rows[0]["JEBZ1"].ToString());
                entity.JeBz2 = decimal.Parse(dt.Rows[0]["JEBZ2"].ToString());
            }
            return entity;
        }

        //根据名称查询客户档案信息
        public DataTable GetByName(string name)
        {
            if (string.IsNullOrEmpty(name))
                return null;
            string sql = string.Format("select *,(CUS_ID + '.' + CUSTOMER+ '.' + F1) as ItemShow  from CUSTOMER_TAB where CUSTOMER like '%{0}%' or ALLNAME like '%{1}%'", name, name);
            return dbHelper.ExecuteDataTable(sql);
        }

        //查询客户档案列表
        public DataTable GetAll()
        {
            string sql = "select *,(CUS_ID + '.' + CUSTOMER+ '.' + F1) as ItemShow  from CUSTOMER_TAB";
            return dbHelper.ExecuteDataTable(sql);
        }

        //查询客户档案列表
        public IList<MisEntity.Customer> FindAll()
        {
            var query = from item in dbContext.Customer
                        select item;
            if (query.Count<MisEntity.Customer>() == 0)
                return null;
            return query.ToList<MisEntity.Customer>();
        }

        //添加客户档案信息
        public int Add(MisModel.Customer obj)
        {
            if (string.IsNullOrEmpty(obj.F1.ToString()))
            {
                obj.F1 = "1";
            }
            string insertSql = "insert into CUSTOMER_TAB(CUS_ID,CUSTOMER,ALLNAME,ADDR,POST,TELE,BEI,LINKER,BANK,BANKNO,TAXNO,F1,F2,F3,F4,F5,F6,F7,F8,FAITHJE,DATE_s,DATE_e,FAITH,DEBT,WAY_JS,SEX,AGE,PROF,DATE_L,BIRTHDAY,MENID,OPER,SUMJEBUY,SUMJEDED,CTYPE,MM,YCDEBT ,JEBZ1,JEBZ2) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}')";
            object[] args = new object[39];
            args[0] = obj.CusID;
            args[1] = obj.Customer_;
            args[2] = obj.AllName;
            args[3] = obj.Addr;
            args[4] = obj.Post;
            args[5] = obj.Tele;
            args[6] = obj.Bei;
            args[7] = obj.Liker;
            args[8] = obj.Bank;
            args[9] = obj.BankNo;
            args[10] = obj.TaxNo;
            args[11] = obj.F1;
            args[12] = obj.F2;
            args[13] = obj.F3;
            args[14] = obj.F4;
            args[15] = obj.F5;
            args[16] = obj.F6;
            args[17] = obj.F7;
            args[18] = obj.F8;
            args[19] = obj.FaiThJe;
            args[20] = obj.DateS;
            args[21] = obj.DateE;
            args[22] = obj.FaiTh;
            args[23] = obj.Debt;
            args[24] = obj.WayJS;
            args[25] = obj.Sex;
            args[26] = obj.Age;
            args[27] = obj.Prof;
            args[28] = obj.DateL;
            args[29] = obj.Birthday;
            args[30] = obj.MenID;
            args[31] = obj.Oper;
            args[32] = obj.SumJeBuy;
            args[33] = obj.SumJeDed;
            args[34] = obj.CType;
            args[35] = obj.MM;
            args[36] = obj.YcDebt;
            args[37] = obj.JeBz1;
            args[38] = obj.JeBz2;
            string sql = string.Format(insertSql, args);
            return dbHelper.ExecuteNonQuery(sql);
        }

        //修改客户档案信息
        public int Update(MisModel.Customer obj)
        {
            string updateSql = "update CUSTOMER_TAB set CUSTOMER = '{1}',ALLNAME = '{2}',ADDR = '{3}',POST = '{4}',TELE = '{5}',BEI = '{6}',LINKER = '{7}',BANK = '{8}',BANKNO = '{9}',TAXNO = '{10}',F1 = '{11}',F2 = '{12}',F3 = '{13}',F4 = '{14}',F5 = '{15}',F6 = '{16}',F7 = '{17}',F8 = '{18}',FAITHJE = '{19}',DATE_s = '{20}',DATE_e = '{21}',FAITH = '{22}',DEBT = '{23}',WAY_JS = '{24}',SEX  = '{25}',AGE = '{26}',PROF = '{27}',DATE_L = '{28}',BIRTHDAY = '{29}',MENID = '{30}',OPER = '{31}',SUMJEBUY = '{32}',SUMJEDED = '{33}',CTYPE = '{34}',MM = '{35}',YCDEBT = '{36}' ,JEBZ1 = '{37}',JEBZ2 = '{38}' where CUS_ID = '{0}'";
            object[] args = new object[39];
            args[0] = obj.CusID;
            args[1] = obj.Customer_;
            args[2] = obj.AllName;
            args[3] = obj.Addr;
            args[4] = obj.Post;
            args[5] = obj.Tele;
            args[6] = obj.Bei;
            args[7] = obj.Liker;
            args[8] = obj.Bank;
            args[9] = obj.BankNo;
            args[10] = obj.TaxNo;
            args[11] = obj.F1;
            args[12] = obj.F2;
            args[13] = obj.F3;
            args[14] = obj.F4;
            args[15] = obj.F5;
            args[16] = obj.F6;
            args[17] = obj.F7;
            args[18] = obj.F8;
            args[19] = obj.FaiThJe;
            args[20] = obj.DateS;
            args[21] = obj.DateE;
            args[22] = obj.FaiTh;
            args[23] = obj.Debt;
            args[24] = obj.WayJS;
            args[25] = obj.Sex;
            args[26] = obj.Age;
            args[27] = obj.Prof;
            args[28] = obj.DateL;
            args[29] = obj.Birthday;
            args[30] = obj.MenID;
            args[31] = obj.Oper;
            args[32] = obj.SumJeBuy;
            args[33] = obj.SumJeDed;
            args[34] = obj.CType;
            args[35] = obj.MM;
            args[36] = obj.YcDebt;
            args[37] = obj.JeBz1;
            args[38] = obj.JeBz2;
            string sql = string.Format(updateSql, args);
            return dbHelper.ExecuteNonQuery(sql);
        }

        //根据ID删除客户档案信息
        public int Delete(string id)
        {
            string sql = string.Format("delete from CUSTOMER_TAB where CUS_ID='{0}'", id);
            return dbHelper.ExecuteNonQuery(sql);
        }
    }
}
